package com.foredu.fiqs.web.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import com.foredu.fiqs.domain.Employee;
import com.foredu.fiqs.domain.Salary;
import com.foredu.fiqs.domain.SalaryItem;
import com.foredu.fiqs.domain.SalaryItemDetail;
import com.foredu.fiqs.domain.SalaryItemDetailType;
import com.foredu.fiqs.domain.SalaryItemType;

import jxl.Cell;
import jxl.CellType;
import jxl.DateCell;
import jxl.LabelCell;
import jxl.NumberCell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WriteException;

public class ReadExcelUtil {

	/**
	 * 
	 * @param is
	 *            上传EXCEL工资报表输入流
	 * @param salaryItemType
	 *            工资条目类型ID
	 * @return 返回
	 * @throws BiffException
	 * @throws IOException
	 * @throws WriteException
	 */
	public static List<Salary> read(InputStream is, SalaryItemType salaryItemType, int year, int month) throws BiffException, IOException, WriteException {

		List<Salary> resultList = new ArrayList<Salary>();
		//获得当前日期，作为工资发放日期
		String payDate = new SimpleDateFormat("yyyy-MM-dd").format(new Date());
		
		// 从输入流读取EXCEL数据表
		try {
			// 从输入流创建Workbook
			Workbook rwb = Workbook.getWorkbook(is);
			// 一旦创建了Workbook，我们就可以通过它来访问Excel Sheet工作表
			Sheet rs = rwb.getSheet(0);
			System.out.println(rwb.getNumberOfSheets());
			System.out.println(rs.getName());
			int rows = rs.getRows();
			int columns = rs.getColumns();
			
			System.out.println("-------rows="+rows+",columns="+columns);

			switch (salaryItemType.getId()) {
			case 1:// 导入档案工资
				for (int i = 2; i < rows; i++) {
					//如有空行，跳过该行，rs.getCell(j,i)，j代表列，i代表行，均从0开始编号
					if (rs.getCell(1, i).getContents()==null || "".equals(rs.getCell(1, i).getContents())) {
						continue;
					}
					
					// 创建工资对象，设置所属雇员及包含的工资条目和工资条目明细
					Salary salary = new Salary();
					salary.setYear(year);
					salary.setMonth(month);
					salary.setPayDate(payDate);
					
					//创建工资条目对象，关联工资对象
					SalaryItem item = new SalaryItem();
					item.setSalary(salary);
					item.setType(salaryItemType);
					Set<SalaryItem> itemSet = new HashSet<SalaryItem>();
					itemSet.add(item);
					salary.setSalaryItems(itemSet);

					//创建教职工对象，关联工资对象
					Employee emp = new Employee();
					emp.setEmpId(rs.getCell(1, i).getContents());
					emp.setNickname(rs.getCell(3, i).getContents());
					salary.setEmployee(emp);

					//创建工资条目明细对象，关联工资条目对象
					Set<SalaryItemDetail> detailSet = new HashSet<SalaryItemDetail>();
					Set<SalaryItemDetailType> types = salaryItemType.getDetailTypes();
					int k = 4;//从第4列开始读取工资条目明细数据
					float total = 0;//工资条目总计
					for (SalaryItemDetailType detailType : types) {
						System.out.println("--------rs.getCell("+k+","+i+")="+rs.getCell(k, i).getContents());
						SalaryItemDetail detail = new SalaryItemDetail();
						String content = rs.getCell(k++, i).getContents();
						if (content==null || content.equals("")) {
							content = "0";
						} else if (content.contains(",")) {
							content = content.replace(",", "");
						}
						detail.setAmount(Float.parseFloat(content));
						total = detail.getAmount();
						detail.setSalaryItem(item);
						detail.setType(detailType);
						detail.setSalaryItem(item);
						detailSet.add(detail);
					}
					//设置工资条目总计
					item.setTotal(total);
					item.setSalaryItemDetails(detailSet);
					
					//设置工资总计
					salary.setTotal(total);
					
					resultList.add(salary);
				}
				break;

			}

			// 操作完成时，关闭对象，释放占用的内存空间
			rwb.close();

		} catch (Exception e) {
			e.printStackTrace();
		}

		return resultList;
	}
}
